import os
import psycopg2
import sqlite3
from flask import g, current_app
from contextlib import contextmanager
from app.utils.log_config import get_logger

# 配置日志
logger = get_logger('db')

# 移除以下两行（避免循环导入）
# from app import create_app
# app = create_app()

def get_db():
    db = getattr(g, '_database', None)
    if db is None:
        # 通过 current_app 获取数据库配置（应用上下文加载后可用）
        if current_app.config.get('DATABASE_TYPE') == 'postgresql':
            # PostgreSQL连接
            db = g._database = psycopg2.connect(
                current_app.config['DATABASE']
            )
            logger.info('PostgreSQL数据库连接成功')
        else:
            # SQLite连接
            db = g._database = sqlite3.connect(current_app.config['DATABASE'])
            db.row_factory = sqlite3.Row  # 支持按列名访问
            logger.info('SQLite数据库连接成功')
    return db

def close_db(exception):
    db = getattr(g, '_database', None)
    if db is not None:
        try:
            db.close()
            logger.info('数据库连接已关闭')
        except Exception as e:
            logger.error(f'关闭数据库连接时出错: {e}')

@contextmanager
def get_db_connection():
    """
    获取数据库连接，支持SQLite和PostgreSQL
    使用上下文管理器确保连接正确关闭
    """
    conn = None
    try:
        if current_app.config.get('DATABASE_TYPE') == 'postgresql':
            # PostgreSQL连接
            conn = psycopg2.connect(current_app.config['DATABASE'])
            conn.autocommit = False  # 启用事务
            logger.info('PostgreSQL数据库连接成功')
        else:
            # SQLite连接
            conn = sqlite3.connect(current_app.config['DATABASE'])
            conn.row_factory = sqlite3.Row
            logger.info('SQLite数据库连接成功')
        
        yield conn
    except Exception as e:
        logger.error(f'数据库连接失败: {e}')
        if conn and hasattr(conn, 'rollback'):
            try:
                conn.rollback()
            except:
                pass
        raise
    finally:
        if conn:
            try:
                conn.close()
                logger.info('数据库连接已关闭')
            except Exception as e:
                logger.error(f'关闭数据库连接时出错: {e}')

def execute_query(conn, query, params=None, commit=False):
    """
    执行SQL查询
    
    Args:
        conn: 数据库连接对象
        query: SQL查询语句
        params: 查询参数（元组）
        commit: 是否提交事务
    
    Returns:
        对于SELECT查询，返回结果集；对于其他查询，返回None
    """
    cursor = None
    try:
        cursor = conn.cursor()
        cursor.execute(query, params or ())
        
        # 如果是SELECT语句，返回结果
        if query.strip().upper().startswith('SELECT'):
            result = cursor.fetchall()
            return result
        
        if commit:
            conn.commit()
            logger.info(f'查询已提交: {query[:50]}...')
    except Exception as e:
        logger.error(f'执行查询失败: {e}')
        if conn and hasattr(conn, 'rollback'):
            conn.rollback()
        raise
    finally:
        if cursor:
            cursor.close()

def add_indexes(conn):
    """
    为数据库表添加必要的索引
    """
    indexes = [
        # 用户表索引
        "CREATE INDEX IF NOT EXISTS idx_users_username ON users(username)",
        # 库存表索引
        "CREATE INDEX IF NOT EXISTS idx_inventory_product_id ON inventory(product_id)",
        "CREATE INDEX IF NOT EXISTS idx_inventory_quantity ON inventory(quantity)",
        # 供应商表索引
        "CREATE INDEX IF NOT EXISTS idx_suppliers_name ON suppliers(name)",
        # 虚拟库存表索引
        "CREATE INDEX IF NOT EXISTS idx_virtual_inventory_product_id ON virtual_inventory(product_id)",
        "CREATE INDEX IF NOT EXISTS idx_virtual_inventory_supplier_id ON virtual_inventory(supplier_id)",
        # 入库记录表索引
        "CREATE INDEX IF NOT EXISTS idx_inbound_records_product_id ON inbound_records(product_id)",
        "CREATE INDEX IF NOT EXISTS idx_inbound_records_date ON inbound_records(inbound_date)",
        # 出库记录表索引
        "CREATE INDEX IF NOT EXISTS idx_outbound_records_product_id ON outbound_records(product_id)",
        "CREATE INDEX IF NOT EXISTS idx_outbound_records_date ON outbound_records(outbound_date)",
        # 零库存订单表索引
        "CREATE INDEX IF NOT EXISTS idx_zero_inventory_orders_product_id ON zero_inventory_orders(product_id)",
        "CREATE INDEX IF NOT EXISTS idx_zero_inventory_orders_status ON zero_inventory_orders(status)"
    ]
    
    for index_sql in indexes:
        try:
            execute_query(conn, index_sql, commit=True)
            logger.info(f'已创建/验证索引: {index_sql}')
        except Exception as e:
            logger.error(f'创建索引失败: {e}')